<?php
/**
 * Created by PhpStorm.
 * User: 沁塵
 * Date: 2019/4/20
 * Time: 19:57
 */

namespace app\api\controller\v1;

use app\api\model\Account;
use app\api\model\ModelDistribute;
use app\api\model\ProductConfig;
use app\api\model\Material;
use app\api\model\ProductScript;
use app\api\model\Upload;
use app\api\model\ModelEvent;
use app\api\model\LinUser;
use app\api\service\token\LoginToken;
use think\db\Where;
use think\Request;
use think\Db;
use think\Exception;

include "../vendor/PHPExcel/Classes/PHPExcel.php";
require_once '../vendor/PHPExcel/Classes/PHPExcel/IOFactory.php';
require_once '../vendor/PHPExcel/Classes/PHPExcel/Reader/Excel5.php';
use PHPExcel;
use PHPExcel_IOFactory;


class Model
{
    public function addEvent(Request $request) {
        $params = $request->post();
        $event = new ModelEvent();
        $uid = LinUser::where("username", $params["model"])->findOrEmpty();
        if (!$uid->isEmpty()) {
            $params["uid"] = $uid["id"];
        }
        $event->create($params);

        return writeJson(200, '', '请求成功');
    }

//    混剪初版领取任务
    public function getEvent(Request $request) {
        $info = ModelEvent::where("status", 1)->where("type", 2)->findOrEmpty();
         if (!$info->isEmpty()) {
             $info->status = 2;
             $info->get_time = date("Y-m-d H:i:s", time());
             $info->save();
             return writeJson(200, $info, '请求成功');
         } else {
             $total = 0;
//             创建任务
//             $fifteenDaysAgo = strtotime('-15 days', time());
//             $query = Material::field('a.pid, COUNT(*) AS num0')
//                 ->alias("a")
//                 ->where('num', 0)
//                 ->where('type', 2)
//                 ->where("create_time", ">=", date('Y-m-d H:i:s', strtotime('midnight', $fifteenDaysAgo)));
//
//             $material = $query->group('a.pid')->select()->toArray();
//
//             if (!empty($material)) {
//                 foreach ($material as $m) {
//                     $shang = $m["num0"] / 5;
//                     if ($shang >= 1) {
//                         for ($i=1; $i<=$shang; $i++) {
//                             //                         创建新混剪任务
//                             $add = [
//                                 "pid"=>$m["pid"],
//                                 "type"=>2,
//                                 "status"=>1,
//                             ];
//                             ModelEvent::create($add);
//                             $total++;
//                         }
//                     }
//                 }
//             }
             return writeJson(200, $total, '无，已创建');
         }

    }

    public function createHjEvent(Request $request)
    {
        $params = $request->get();

        $total = 0;
        $fifteenDaysAgo = strtotime('-15 days', time());
        $query = Material::field('a.pid, COUNT(*) AS num0')
            ->alias("a")
            ->where('num', 0)
            ->where('type', 2)
            ->where("create_time", ">=", date('Y-m-d H:i:s', strtotime('midnight', $fifteenDaysAgo)));

        if (isset($params["pid"])) {
            $query = $query->where("a.pid", $params["pid"]);
        }
        if (isset($params["category_hj"])) {
            $query = $query->join("product_config b", "a.pid=b.pid")
                ->where("category_hj", $params["category_hj"]);
        }

        $material = $query->group('a.pid')->select()->toArray();

        if (!empty($material)) {
            foreach ($material as $m) {
                $shang = $m["num0"] / 5;
                if ($shang >= 1) {
                    for ($i=1; $i<=$shang; $i++) {
                        //                         创建新混剪任务
                        $add = [
                            "pid"=>$m["pid"],
                            "type"=>2,
                            "status"=>1,
                        ];
                        ModelEvent::create($add);
                        $total++;
                    }
                }
            }
        }
        return writeJson(200, $total, '无，已创建');
    }

    public function backfillEvent(Request $request)
    {
        $params = $request->post();
        $info = ModelEvent::where("id", $params["id"])->findOrEmpty();
        if (!$info->isEmpty()) {
            $info->save($params);
            return writeJson(200,$info,'成功！');
        } else {
            return writeJson(400,[],'找不到！');
        }
    }

    public function list(Request $request)
    {
        $params = $request->post();
        $uid = LoginToken::getInstance()->getCurrentUid();

        $pageSize = $params['pageSize'] ?? 50;
        $upload = new ModelEvent();

        $type = 1;
        if (isset($params["type"])) {
            $type = $params["type"];
        }
        if ($params["type"] == 2) {
            $query = $upload->field("a.*,b.pname,b.category_hj")->alias('a')
                ->join('product_config b', 'a.pid = b.pid')
                ->where("type", $type);
            if (!empty($params["category_hj"])) {
                $query = $query->where("b.category_hj", $params["category_hj"]);
            }
        } else {
            $query = $upload->where("type", $type);
        }

        if ($uid != 1) {
            $query = $query->where("uid", $uid);
        }
        if (!empty($params["pid"])) {
            $query = $query->whereLike("pid", "%".$params["pid"]."%");
        }
        if (!empty($params["pname"])) {
            $query = $query->whereLike("pname", "%".$params["pname"]."%");
        }
        if (!empty($params["model"])) {
            $query = $query->whereLike("model", "%".$params["model"]."%");
        }
        if (!empty($params["status"])) {
            $query = $query->where("status", $params["status"]);
        }
        if (!empty($params["start_time"])) {
            $query = $query->where("create_time", ">", $params["start_time"]);
        }
        if (!empty($params["jd_export"])) {
            if ($params["jd_export"] == 1) {
                $query = $query->where("jd_export", $params["jd_export"]);
            } else {
                $query = $query->whereNull("jd_export");
            }
        }
        if (!empty($params["tb_export"])) {
            if ($params["tb_export"] == 1) {
                $query = $query->where("tb_export", $params["tb_export"]);
            } else {
                $query = $query->whereNull("tb_export");
            }
        }
        $list = $query->order("id desc")->paginate($pageSize);
        return writeJson(200,$list,'数据列表！');

    }

    public function usableModel(Request $request)
    {
        $params = $request->get();

//        每天每个模特2条视频
        $models = ["陈嘉雯","魏嘉慧","郑根春","李夏希","王露绮","李玫琳","朱雨希","彭凯盈","朱虹","林抒语","费心怡","陈舒琦","兰淼芳","何灵敏","陈菲菲"];
//        $models = ["郑洁"];
        $used_model = ModelDistribute::field("model, count(*) as count")
            ->where("create_time", ">", date('Y-m-d 00:00:00',time()))
            ->group("model")->having("count >= 20")
            ->select()->toArray();

        $used_models = [];
        foreach ($used_model as $item) {
            $used_models[] = $item["model"];
        }

//        去掉满20的号
        $diff_model = array_diff($models, $used_models);

        $useful_model = Upload::field("model")->distinct(true)
            ->where("product_id", $params["pid"])
            ->where("platform", "逛逛")
//            ->whereNull("is_kong")
            ->whereNull("in_use")
            ->select()->toArray();

        $useful_models = [];
        foreach ($useful_model as $item) {
            $useful_models[] = $item["model"];
        }
//        去掉没有开头的号
        $diff_diff_model = array_intersect($diff_model, $useful_models);

        $model = $diff_diff_model[array_rand($diff_diff_model)];

        return writeJson(200,$model,'成功！');

    }

    public function createModelDis(Request $request)
    {
        $params = $request->post();
        $event = new ModelDistribute();
        $res = $event->create($params);

        return writeJson(200, $res["id"], '请求成功');
    }

    public function getOrderEvent()
    {
        $event = new ModelEvent();
        $info = $event->where("status", 11)->findOrEmpty();
        if (!$info->isEmpty()) {
            $material = Material::field("num,url,filename")->where("main_task_id", $info["main_task_id"])
                ->where("type", 1)->select()->toArray();
            $info["material"] = $material;

            $info->status = 12;
            $info->save();
            return writeJson(200, $info, '请求成功');
        } else {
            return writeJson(200, [], '无');
        }
    }

//    逛逛三版领取任务
    public function getVideoEvent(Request $request) {
        $event = new ModelEvent();
        $info = $event->where("status", 1)->where("type", 1)
//            ->where("id", 41660)
            ->where(function ($gather){
                $ten_min_ago = date("Y-m-d H:i:s", time() - 700);
                $gather->whereNull('get_time')->whereOr('get_time','<',$ten_min_ago);
            })
            ->findOrEmpty();

        if (!$info->isEmpty()) {
//          检查口型配好没
            $upload = Upload::where("main_task_id", $info["main_task_id"])->whereNull("qiniu")->count();
            if ($upload == 0) {
//              配好了
                $jiaoben_info = ProductScript::field("script, title")->where("pid", $info["pid"])
                    ->where("model", $info["model"])
                    ->where("script_name", $info["script_name"])->findOrEmpty();
                $script = $jiaoben_info["script"];

                $upload_info = Upload::where("main_task_id", $info["main_task_id"])->select()->toArray();
                $material = Material::field("num,url")->where("main_task_id", $info["main_task_id"])
                    ->where("type", 1)->select()->toArray();

                $json_script = json_decode($script, true);
                foreach ($json_script as &$s) {
                    foreach ($upload_info as $u) {
                        if ($s["v"] == $u["number"] && $s["kou"] == 1) {
                            $s["path"] = $u["qiniu"];
                            $s["audio"] = $u["product_audio_url"];
                        }
                    }
                    foreach ($material as $m) {
                        if ($s["v"] == $m["num"] && $s["kou"] == 0) {
                            $s["path"] = $m["url"];
                        }
                    }
                }

                $info->status = 2;
                $info->get_time = date("Y-m-d H:i:s", time());
                $info->save();

                $info["jiaoben"] = $json_script;
                $info["title"] = $jiaoben_info["title"];

                return writeJson(200, $info, '请求成功');

            } else {
                $info->get_time = date("Y-m-d H:i:s", time());
                $info->save();
                return writeJson(200, [], '无');
            }
        } else {
            return writeJson(200, [], '无');
        }
    }

    public function getListById(Request $request)
    {
        $params = $request->get();
        $list = ModelEvent::alias('a')
            ->field("a.*, b.category")
            ->join('product_config b', 'a.pid = b.pid')
            ->whereIn("a.id", $params["ids"])->where("status", 3)->select()->toArray();

        return writeJson(200, $list, '请求成功');

    }

    public function importJdExport(Request $request){
        $file = $request->file('file');
        $event = new ModelEvent();

        $filename  = $file->getInfo()['tmp_name'];
        //设置excel格式
        $reader = PHPExcel_IOFactory::createReader('Excel2007');
        //载⼊excel⽂件
        $excel = $reader->load($filename);
        //读取第⼀张表
        $sheet = $excel->getSheet(0);
        //获取总⾏数
        $row_num = $sheet->getHighestRow();

        //获取总列数
        $col_num = 'B';
        $datas = []; //数组形式获取表格数据
        $head = [];
        $res = [];

        for($row=1;$row<=$row_num;$row++)
        {
            for($col='A';$col<=$col_num;$col++) {
                if ($row == 1) {
                    $head[] = $sheet->getCell($col."1")->getValue();
                } else {
                    $datas[$row-2][] = $sheet->getCell($col.$row)->getValue();
                }
            }
        }
//        $array2 = ["id","jd_export"];
//        if (array_diff_assoc($head, $array2) != NULL) {
//            return writeJson(200,false,'表结构不正确！');
//        }

        DB::startTrans();
        try {
            $save_data = [];

            for ($row = 0; $row < count($datas); $row++) {
                $add = [];
                $add["id"] = $datas[$row][0];
                $add["jd_export"] = $datas[$row][1];
//                $add["jd_url"] = $datas[$row][2];
//                $add["jd_backtime"] = date("Y-m-d H:i:s");
                $save_data[] = $add;
            }
            if (count($save_data) > 0) {
                $res = $event->saveAll($save_data);
            }
            // 提交事务
            Db::commit();
        }catch (Exception $e) {
            // 回滚事务
            Db::rollback();
            return writeJson(200,false,'导表失败！');
        }
        if (count($res) > 0) {
            return writeJson(200,count($res),'完成！');
        } else {
            return writeJson(200,0,'失败了！');

        }
    }

    /**
     * 发布统计
     */
    public function pubCount(Request $request)
    {
        $start_time = date('Y-m-d 00:00:00',time());
        $list = ModelEvent::where("type", 1)
            ->where(function ($query) use ($start_time) {
                $query->where('xhs_backtime', '>=', $start_time)
                    ->whereOr('jd_backtime', '>=', $start_time)
                    ->whereOr('tb_backtime', '>=', $start_time);
            })
//            ->fetchSql()->select();
            ->select()->toArray();
        $data = [];
        $model = [];
        // 一级 model
        if (!empty($list)) {
            foreach ($list as $li) {
                if (in_array($li["model"], $model)) {
                    continue;
                } else {
                    $item = [];
                    $item["name"] = $li["model"];
                    $item["data"] = array(
                        "xhs"=>0,
                        "dy"=>0,
                        "tb"=>0
                    );
                    $model[] = $li["model"];
                    $data[] = $item;
                }
            }

            foreach ($list as $li) {
                if ($li["xhs_backtime"] >= $start_time) {
                    foreach ($data as &$da) {
                        if ($da["name"] == $li["model"]) {
                            $da["data"]["xhs"] = $da["data"]["xhs"] + 1;
                            break; // 找到后退出循环
                        }
                    }
                }
                if ($li["tb_backtime"] >= $start_time) {
                    foreach ($data as &$da) {
                        if ($da["name"] == $li["model"]) {
                            $da["data"]["tb"] = $da["data"]["tb"] + 1;
                            break; // 找到后退出循环
                        }
                    }
                }
                if ($li["jd_backtime"] >= $start_time) {
                    foreach ($data as &$da) {
                        if ($da["name"] == $li["model"]) {
                            $da["data"]["jd"] = $da["data"]["jd"] + 1;
                            break; // 找到后退出循环
                        }
                    }
                }
            }
        }
        return writeJson(200, $data, '请求成功');

    }

    public function resetEvent(Request $request)
    {
        $params = $request->get();
        $info = ModelEvent::where("id", $params["id"])->findOrEmpty();
        if (!$info->isEmpty()) {
            if ($info["status"] == 14) {
                $info->status = 11;
            }
            if ($info["status"] == 4) {
                $info->status = 1;
            }
            if ($info["status"] == 3) {
                Upload::where("main_task_id", $info["main_task_id"])->delete();
                $info->status = 11;
            }
            $info->save();
        }
        return writeJson(200, true, '请求成功');

    }

    /**
     * 人设视频导出 用于发布京东
     */
    public function exportForJD(Request $request)
    {
        $params = $request->get();
        $fifteenDaysAgo = strtotime('-15 days', time());

        DB::startTrans();
        try {
            $query = ModelEvent::field("a.id,a.pid,b.pname,b.category_hj,url,cover,model,remark")
                ->alias("a")
                ->join("product_config b", "a.pid=b.pid")
                ->where("a.status", 3)
                ->where("a.type", $params["type"])
                ->where("create_time", ">=", date('Y-m-d H:i:s', strtotime('midnight', $fifteenDaysAgo)));;

            if (isset($params["ids"]) && $params["ids"] != "") {
                $query = $query->whereIn("a.id", $params["ids"]);
            }
            if (isset($params["category_hj"]) && $params["category_hj"] != "") {
                $query = $query->where("b.category_hj", $params["category_hj"]);
            }
            if ($params["type"] == 2) {
                $query = $query->whereNull("tb_export");
            } else {
                $query = $query->whereNull("jd_export");
            }
            $list = $query->select()->toArray();

            if (!empty($list)) {
                if ($params["type"] == 1) {
                    $xlsName = date('Ymd')."_".$list[0]["model"]; // 表名称
                    $xlsCell = [
                        ['id', 'ID'],
                        ['pid', '产品id'],
                        ['pname', '产品名'],
                        ['url', '视频链接'],
                        ['cover', '封面'],
                        ['category_hj', '垂类'],
                        ['model', '模特']
                    ];// 表头信息
                } else {
                    $xlsName = date('YmdHis')."_混剪"; // 表名称
                    $xlsCell = [
                        ['id', 'ID'],
                        ['pid', '产品id'],
                        ['pname', '产品名'],
                        ['category_hj', '垂类'],
                        ['url', '视频链接'],
                        ['cover', '封面'],
                        ['remark', '备注']
                    ];// 表头信息
                }

                $this->downloadExcel($xlsName, $xlsCell, $list);// 传递参数
                $ids = [];
                foreach ($list as $li) {
                    $ids[] = $li["id"];
                }
                if ($params["type"] == 1) {
                    $res = ModelEvent::whereIn("id", $ids)->update(["jd_export"=>1, "jd_backtime"=>date('Y-m-d H:i:s', time())]);
                } else {
                    $res = ModelEvent::whereIn("id", $ids)->update(["tb_export"=>1, "tb_backtime"=>date('Y-m-d H:i:s', time())]);

                }
            }
            // 提交事务
            Db::commit();
        }catch (Exception $e) {
            // 回滚事务
            Db::rollback();
            return writeJson(20001, $e, '请求失败');
        }
        return writeJson(200, $res, '请求成功');
    }

    public function downloadExcel($expTitle, $expCellName, $expTableData)
    {
        $xlsTitle    = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $cellNum     = count($expCellName);// 单元格长度
        $dataNum     = count($expTableData);
        $objPHPExcel = new \PHPExcel();// 引入库
        $cellName = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
            'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
        ];
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue($cellName[$i] . '1', $expCellName[$i][1]);
        }

        // Miscellaneous glyphs, UTF-8  循环写入数据
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)
                    ->setCellValue($cellName[$j] . ($i + 2), (string)$expTableData[$i][$expCellName[$j][0]]);
            }
        }
        ob_end_clean();//这一步非常关键，用来清除缓冲区防止导出的excel乱码
        $PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");//创建生成的格式
        header('Content-Disposition: attachment;filename="'.$xlsTitle.'.xlsx"');//下载下来的表格名
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        return $PHPWriter->save('php://output');
    }

//    逛逛人设视频同步到京东去
    public function sync(Request $request)
    {
        $params = $request->post();
        $p_config = ProductConfig::where("pid", $params["pid"])->findOrEmpty();
        $accounts = Account::where("platform", "京东")->where("model", $params["model"])
            ->where("category", $p_config["category"])->select()->toArray();

    }
}